How to construct an import data file
Tip: Whether constructing a .txt, .csv or .xml data file, it is necessary to use the back-end name for EMu fields. Details about how to locate the back-end name for a field can be found here.
Notes
- When you construct your Import data file to update records and include a Column Name but do not associate a value with it, any existing value in that field in EMu will be removed when the Import is run. This can be useful if you wish to clear any existing values from a particular field. However it can also be potentially dangerous as you could inadvertently clear a field of all values.
- When following the examples in the following pages, be sure to update values to reflect spelling in your environment (for example, when specifying Party Types, change Organization to Organisation if appropriate for your EMu environment).
Three file formats are supported for the import of data into EMu:
- Comma Separated Values or CSV (.csv)
Can be generated using a product like MS Excel and saving files as .csv.
- Tab Separated Values or TSV (.txt or .tab)
Can be generated using a text tool, such as Notepad, or a product like MS Excel and saving files as .txt.
The same rules that apply to .csv files apply to .txt with the exception that values are separated using tabs rather than commas.
How to Save a file as .csv or .txt in MS ExcelIn the following examples, MS Excel is used to create both .txt and .csv file types by selecting .txt or .csv from the Save as type drop list in the Save As box:
Note: MS Excel will attempt to apply its default date format to values that are formatted as a Date data type. This can have undesirable consequences if Excel's default date format is different from the format you require. It is therefore recommended that date columns are formatted as text rather than Date data types. To avoid any ambiguity, it is recommended that the date values are also formatted as, e.g. 24-Nov-2006 (using the text version of a month and full year).
- eXtensible Markup Language or XML (.xml)
Provides the greatest flexibility in specifying the data to be imported and is recommended when using the Import tool to import more than the most basic data structures (for instance, when specifying records with attachments, nested tables, etc.).
An easy method to generate the correctly structured XML for the fields you wish to import or update in EMu is to create an XML Report in EMu:
- In the module in which data is to be imported or updated, create an XML Document report and include the fields to be imported or updated.
- Run the report. An XML document is generated. The format of the report is the same as that required for an import.
When importing data into EMu there are six kinds of field that can be specified in an Import data file:
- Atomic (a single value field), e.g. First: (Person Details) in the Parties module.
- Table (a field which can hold multiple values, one per row), e.g. Other Names: (Person Details) in the Parties module.
- Nested Table (a table within a table: the table is a field which can hold multiple values, one per row).
- Atomic reference (a single value field that references another record), e.g. Borrower/Lender: (Loan Details) in the Loans module.
- Table reference (a field that can hold multiple values, one per row, each one a reference to another record), e.g. Associated With in the Parties module.
- Nested Table reference (a table within a table: the table is a field which can hold multiple values, one per row, each one a reference to another record, e.g. Assigned To: (Task Information) on the Tasks tab (in many modules).
4, 5 and 6 are variations of the first three and specify attachments to atomic, table and nested table fields (that is, the fields do not contain data but references to other records).
This section demonstrates how to construct an import data file in the three supported formats. The approach is to use examples, each one building on the previous. It is therefore suggested that the examples are worked through in sequence.